package com.dob.dbo.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import com.dob.model.Employee;
public class NamedParameterJDBCTemplateExample {

	
	@Autowired
	private DataSource dataSource;
	private NamedParameterJdbcTemplate namedParameterJDBCTemplate;
	private JdbcTemplate jdbcTemplate;

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		this.jdbcTemplate= new JdbcTemplate(dataSource);
		this.namedParameterJDBCTemplate= new NamedParameterJdbcTemplate(dataSource);
	}
	
	public void insertEmployeeUsingNamedParameter(Employee emp){
		String sql="INSERT INTO EMPLOYEE(EMPNAME,EMPID,SALARY) VALUES(:empName,:empId,:salary)";
		SqlParameterSource namedParams= new MapSqlParameterSource("empName",emp.getEmpName())
												.addValue("empId", emp.getEmpId())
												.addValue("salary", emp.getSalary());
		namedParameterJDBCTemplate.update(sql,namedParams);
	}
	
	public Employee getEmployee(int empId){
		String sql="SELECT * FROm EMPLOYEE WHERE EMPID=?";
		return jdbcTemplate.queryForObject(sql, new Object[]{empId}, new EmployeeMappper());
	}
	
	public List<Employee> getAllEmployee(){
		String sql="SELECT * FROM EMPLOYEE";
		return jdbcTemplate.query(sql, new EmployeeMappper());
	}
	
	class EmployeeMappper implements RowMapper<Employee>{

		@Override
		public Employee mapRow(ResultSet rs, int rowNo)
				throws SQLException {
			Employee emp= new Employee();
			emp.setEmpId(rs.getInt("EMPID"));
			emp.setEmpName(rs.getString("EMPNAME"));
			emp.setSalary(rs.getInt("SALARY"));
			return emp;
		}
		
		
	}
				
				
}

